package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.data.BranchChangeLogDataList;
import com.mbc.common.entities.BranchChangeLog;
import com.mbc.common.param.CriteriaParams;

public class BranchChangeLogDAOImpls extends DAO<BranchChangeLog> implements BranchChangeLogDAO {

	public BranchChangeLogDAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "BranchChangeLog";
	}
	
	@Override
	public void create(BranchChangeLog entity) throws SQLException {
		query = "insert into " + table_name + "(LoginId, BranchId, ChangedDate, ChangedBy) values( ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getLoginId());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setTimestamp(i++, new java.sql.Timestamp(entity.getChangedDate().getTime()));
		pstmt.setString(i++, entity.getChangedBy());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public BranchChangeLogDataList getLogs(CriteriaParams cri)
			throws SQLException {
		BranchChangeLogDataList l_DataList = new BranchChangeLogDataList();
		ArrayList<BranchChangeLog> l_entityList = new ArrayList<BranchChangeLog>();
		String l_cri = " and a.LoginId = '" + cri.getKeyword() + "'";
				
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by ChangedDate desc) as [rowno], a.*, b.BranchCode, b.Name as BranchName from " + table_name + " a" +
			" inner join Branch b on a.BranchId = b.BranchId" +
			" where 1 = 1" + l_cri + ") " + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			BranchChangeLog l_entity = readRecord(rs);
			l_entity.setBranchCode(rs.getString("BranchCode"));
			l_entity.setBranchName(rs.getString("BranchName"));
			l_entityList.add(l_entity);
		} 
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		l_cri = " and LoginId = '" + cri.getKeyword() + "'";
		query = "select count(LoginId) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected BranchChangeLog readRecord(ResultSet rs) throws SQLException {
		BranchChangeLog l_entity = new BranchChangeLog();
		l_entity.setLoginId(rs.getString("LoginId"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		l_entity.setChangedDate(rs.getTimestamp("ChangedDate"));
		l_entity.setChangedBy(rs.getString("ChangedBy"));
		return l_entity;
	}

}
